package com.sxhnpg.dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;


import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import com.sxhnpg.model.TravelAgencyInfo;
import com.sxhnpg.model.User;
import com.sxhnpg.util.DBUtil;


public class TravelDao extends BaseDao {

	public Class getEntityClass() {
		return TravelAgencyInfo.class;
	}
	
	
	// 旅行社基本信息列表
	public List<TravelAgencyInfo> findTravelListList(){
		List<TravelAgencyInfo> travelList = null;
		Session session = getSession();
		if( session != null ){
			Criteria criteria =getSession().createCriteria(getEntityClass());
			criteria.addOrder(Order.asc("registerDate"));
			travelList = criteria.list();
			
		}		
		return travelList;
	}
	
	// ajax
	public TravelAgencyInfo findTravelInfoByName(String travelAgencyName){
		TravelAgencyInfo travelAgencyInfo=null;
		Session session = getSession();
		if( session != null ){
			Criteria criteria =getSession().createCriteria(getEntityClass());
			criteria.add(Restrictions.eq("travelAgencyName", travelAgencyName));
			
			List<TravelAgencyInfo> travelAgencyInfoList = criteria.list();
			
			if(travelAgencyInfoList!=null && travelAgencyInfoList.size()>0) {
				travelAgencyInfo = travelAgencyInfoList.get(0);
			}
		}		
		return travelAgencyInfo;
	}
	
	// 基本信息
	public TravelAgencyInfo findBasic(String agencyId){
		TravelAgencyInfo travelAgencyInfo=null;
		Session session = getSession();
		if( session != null ){
			Criteria criteria =getSession().createCriteria(getEntityClass());
			criteria.add(Restrictions.eq("agencyId", agencyId));
			
			List<TravelAgencyInfo> travelAgencyInfoList = criteria.list();
			
			if(travelAgencyInfoList!=null && travelAgencyInfoList.size()>0) {
				travelAgencyInfo = travelAgencyInfoList.get(0);
			}
		}		
		return travelAgencyInfo;
	}
	
	// 基本信息
	public TravelAgencyInfo findTravel(String tableName,int agencyId){
		TravelAgencyInfo travelAgencyInfo=null;
		Connection con = null;
		try{
			con = DBUtil.getConnection();	//获得连接
			String sql = "SELECT * FROM " + tableName + " WHERE AGENCY_ID =?" ;
			PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
			pstmt.setInt(1, agencyId);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				// 构造对象信息
				travelAgencyInfo = new TravelAgencyInfo();
				
				travelAgencyInfo.setAgencyId(String.valueOf(rs.getInt("AGENCY_ID")));
				travelAgencyInfo.setTravelAgencyName(rs.getString("TRAVEL_AGENCY_NAME"));
				travelAgencyInfo.setCarNumber(rs.getString("CAR_NUMBER"));
				travelAgencyInfo.setCarType(rs.getString("CAR_TYPE"));
				travelAgencyInfo.setCountPeople(rs.getInt("COUNT_PEOPLE"));
				travelAgencyInfo.setCity(rs.getString("CITY"));
				travelAgencyInfo.setViewpoint(rs.getString("VIEWPOINT"));
				travelAgencyInfo.setIsDriver(rs.getInt("IS_DRIVER"));
				travelAgencyInfo.setDriver(rs.getString("DRIVER"));
				travelAgencyInfo.setGuide(rs.getString("GUIDE"));
				travelAgencyInfo.setIsContract(rs.getInt("IS_CONTRACT"));
				travelAgencyInfo.setRegisterDate(rs.getTimestamp("REGISTER_DATE"));
				travelAgencyInfo.setRemainTime(rs.getString("REMAIN_TIME"));
				travelAgencyInfo.setSubsidyPrice(rs.getString("SUBSIDY_PRICE"));
				travelAgencyInfo.setTravelerPrice(rs.getString("TRAVELER_PRICE"));
				travelAgencyInfo.setOperatePeople(rs.getString("OPERATE_PEOPLE"));
				travelAgencyInfo.setLastPlace(rs.getString("LAST_PLACE"));
				travelAgencyInfo.setMobile(rs.getString("MOBILE"));
				travelAgencyInfo.setPayType(rs.getString("PAY_TYPE"));
				travelAgencyInfo.setTotalPrice(rs.getString("TOTAL_PRICE"));
				travelAgencyInfo.setHandle(rs.getInt("HANDLE"));
				
				travelAgencyInfo.setIsDedicated(rs.getString("IS_DEDICATED"));
				
			}
			
			rs.close();
			pstmt.close();	// 关闭会话
			
			
		}catch(Exception e){
			logger.error("获得数据失败：" + e);
		}
		return travelAgencyInfo;
	}
	
	// 当前月登记列表
	public List<TravelAgencyInfo> findTravelListByMonthTableName(String tableName){
		Connection con = null;
		List<TravelAgencyInfo> list = new ArrayList<TravelAgencyInfo>();
		try{
			con = DBUtil.getConnection();	//获得连接
			String sql = "SELECT * FROM " + tableName + " ORDER BY REGISTER_DATE desc";
			PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
			ResultSet rs = null;
			pstmt.setFetchSize(100);
			if(pstmt.execute()){
				rs = pstmt.getResultSet();
				while(rs.next()){
					// 构造对象信息
					TravelAgencyInfo travelAgencyInfo = new TravelAgencyInfo();
					
					travelAgencyInfo.setAgencyId(String.valueOf(rs.getInt("AGENCY_ID")));
					travelAgencyInfo.setTravelAgencyName(rs.getString("TRAVEL_AGENCY_NAME"));
					travelAgencyInfo.setCarNumber(rs.getString("CAR_NUMBER"));
					travelAgencyInfo.setCarType(rs.getString("CAR_TYPE"));
					travelAgencyInfo.setCountPeople(rs.getInt("COUNT_PEOPLE"));
					travelAgencyInfo.setCity(rs.getString("CITY"));
					travelAgencyInfo.setViewpoint(rs.getString("VIEWPOINT"));
					travelAgencyInfo.setIsDriver(rs.getInt("IS_DRIVER"));
					travelAgencyInfo.setDriver(rs.getString("DRIVER"));
					travelAgencyInfo.setGuide(rs.getString("GUIDE"));
					travelAgencyInfo.setIsContract(rs.getInt("IS_CONTRACT"));
					travelAgencyInfo.setRegisterDate(rs.getTimestamp("REGISTER_DATE"));
					travelAgencyInfo.setRemainTime(rs.getString("REMAIN_TIME"));
					travelAgencyInfo.setSubsidyPrice(rs.getString("SUBSIDY_PRICE"));
					travelAgencyInfo.setTravelerPrice(rs.getString("TRAVELER_PRICE"));
					travelAgencyInfo.setOperatePeople(rs.getString("OPERATE_PEOPLE"));
					travelAgencyInfo.setLastPlace(rs.getString("LAST_PLACE"));
					travelAgencyInfo.setMobile(rs.getString("MOBILE"));
					travelAgencyInfo.setPayType(rs.getString("PAY_TYPE"));
					travelAgencyInfo.setTotalPrice(rs.getString("TOTAL_PRICE"));
					travelAgencyInfo.setHandle(rs.getInt("HANDLE"));
					
					travelAgencyInfo.setIsDedicated(rs.getString("IS_DEDICATED"));
					
					list.add(travelAgencyInfo);
				}
			}
			rs.close();
			pstmt.close();	// 关闭会话
			
			
		}catch(Exception e){
			logger.error("获得当前月列表失败：" + e);
		}
		return list;
	}
	
	// 插入数据
	public void insertTravel(String tableName , TravelAgencyInfo travelAgencyInfo){
		Connection con = null;
		try{
			con = DBUtil.getConnection();	//获得连接
			String sql = "INSERT INTO " + tableName + " (TRAVEL_AGENCY_NAME ,CAR_NUMBER ,CAR_TYPE ,COUNT_PEOPLE ,CITY ," +
					" VIEWPOINT , IS_DRIVER ,DRIVER ,GUIDE ,IS_CONTRACT ,REGISTER_DATE ,REMAIN_TIME ,SUBSIDY_PRICE ,TRAVELER_PRICE ," +
					" OPERATE_PEOPLE ,LAST_PLACE ,MOBILE ,PAY_TYPE ,TOTAL_PRICE ,HANDLE,IS_DEDICATED) " +
					" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
			PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
			pstmt.setString(1, travelAgencyInfo.getTravelAgencyName());
			pstmt.setString(2, travelAgencyInfo.getCarNumber());
			pstmt.setString(3, travelAgencyInfo.getCarType());
			pstmt.setInt(4, travelAgencyInfo.getCountPeople());
			pstmt.setString(5, travelAgencyInfo.getCity());
			pstmt.setString(6, travelAgencyInfo.getViewpoint());
			pstmt.setInt(7, travelAgencyInfo.getIsDriver());
			pstmt.setString(8, travelAgencyInfo.getDriver());
			pstmt.setString(9, travelAgencyInfo.getGuide());
			pstmt.setInt(10, travelAgencyInfo.getIsContract());
 			pstmt.setTimestamp(11, new Timestamp(travelAgencyInfo.getRegisterDate().getTime()));
			pstmt.setString(12, travelAgencyInfo.getRemainTime());
			pstmt.setString(13, travelAgencyInfo.getSubsidyPrice());
			pstmt.setString(14, travelAgencyInfo.getTravelerPrice());
			pstmt.setString(15, travelAgencyInfo.getOperatePeople());
			pstmt.setString(16, travelAgencyInfo.getLastPlace());
			pstmt.setString(17, travelAgencyInfo.getMobile());
			pstmt.setString(18, travelAgencyInfo.getPayType());
			pstmt.setString(19, travelAgencyInfo.getTotalPrice());
			pstmt.setInt(20, travelAgencyInfo.getHandle());
			pstmt.setString(21, travelAgencyInfo.getIsDedicated());
			pstmt.execute();
			
			pstmt.close();	// 关闭会话
			
			
		}catch(Exception e){
			logger.error("登记信息失败：" + e);
		}
	}
	
	// 删除数据
	public void removeTravel(String tableName,int id){
		Connection con = null;
		try{
			con = DBUtil.getConnection();	//获得连接
			String sql = "DELETE FROM " + tableName + " WHERE AGENCY_ID	=?";
			PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
			pstmt.setInt(1, id);
			pstmt.execute();
			
			pstmt.close();	// 关闭会话
			
		}catch(Exception e){
			logger.error("删除信息失败：" + e);
		}
	}
	
	// 修改数据
	public void updateTravel(String tableName,TravelAgencyInfo travelAgencyInfo){
		Connection con = null;
		try{
			con = DBUtil.getConnection();	//获得连接
			String sql = "UPDATE " + tableName + " SET TRAVEL_AGENCY_NAME =? ,CAR_NUMBER =? ,CAR_TYPE =? ,COUNT_PEOPLE =? ,CITY =? ," +
				" VIEWPOINT =? , IS_DRIVER =? ,DRIVER =? ,GUIDE =? ,IS_CONTRACT =? ,REMAIN_TIME =? ,SUBSIDY_PRICE =? ,TRAVELER_PRICE =? ," +
				" OPERATE_PEOPLE =? ,LAST_PLACE =? ,MOBILE =? ,PAY_TYPE =? ,TOTAL_PRICE =? ,IS_DEDICATED =? " +
				" WHERE AGENCY_ID = ? " ;
			PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
			
			pstmt.setString(1, travelAgencyInfo.getTravelAgencyName());
			pstmt.setString(2, travelAgencyInfo.getCarNumber());
			pstmt.setString(3, travelAgencyInfo.getCarType());
			pstmt.setInt(4, travelAgencyInfo.getCountPeople());
			pstmt.setString(5, travelAgencyInfo.getCity());
			pstmt.setString(6, travelAgencyInfo.getViewpoint());
			pstmt.setInt(7, travelAgencyInfo.getIsDriver());
			pstmt.setString(8, travelAgencyInfo.getDriver());
			pstmt.setString(9, travelAgencyInfo.getGuide());
			pstmt.setInt(10, travelAgencyInfo.getIsContract());
			pstmt.setString(11, travelAgencyInfo.getRemainTime());
			pstmt.setString(12, travelAgencyInfo.getSubsidyPrice());
			pstmt.setString(13, travelAgencyInfo.getTravelerPrice());
			pstmt.setString(14, travelAgencyInfo.getOperatePeople());
			pstmt.setString(15, travelAgencyInfo.getLastPlace());
			pstmt.setString(16, travelAgencyInfo.getMobile());
			pstmt.setString(17, travelAgencyInfo.getPayType());
			pstmt.setString(18, travelAgencyInfo.getTotalPrice());
			pstmt.setString(19, travelAgencyInfo.getIsDedicated());
			pstmt.setInt(20, Integer.parseInt(travelAgencyInfo.getAgencyId()));
			
			pstmt.executeUpdate();
			
			pstmt.close();	// 关闭会话
			
		}catch(Exception e){
			logger.error("修改信息失败：" + e);
		}
	}
	
	// 处理信息
	public void updateHandle(String tableName,int agencyId){
		Connection con = null;
		try{
			con = DBUtil.getConnection();	//获得连接
			String sql = "UPDATE " + tableName + " SET HANDLE =?  " +
				" WHERE AGENCY_ID = ? " ;
			PreparedStatement pstmt = con.prepareStatement(sql);	//预编译
			
			pstmt.setInt(1, 1);
			pstmt.setInt(2, agencyId);
			
			pstmt.executeUpdate();
			
			pstmt.close();	// 关闭会话
			
		}catch(Exception e){
			logger.error("处理信息失败：" + e);
		}
	}
}
